﻿/**
* CRL
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;
using CRL.Data.DBAccess;
using CRL.Core.Extension;
using CRL.Data.Attribute;
using System.Threading.Tasks;
using System.Collections;
using CRL.Data.DBExtend;
using System.Collections.Concurrent;

namespace CRL.Data.DBAdapter
{
    public abstract class DBAdapterBase
    {
        //internal DbContext dbContext;
        //protected DBHelper helper;
        protected DBType dbType;
        public DBAdapterBase(DbContextInner _dbContext)
        {
            //dbContext = _dbContext;
            //helper = dbContext.DBHelper;
            dbType = _dbContext.DBHelper.CurrentDBType;
        }
        /// <summary>
        /// 是否支持编译存储过程
        /// </summary>
        public virtual bool CanCompileSP
        {
            get
            {
                return false;
            }
        }
        static Dictionary<DBType, DBAdapterBase> DBAdapterBaseCache = new Dictionary<DBType, DBAdapterBase>();
        /// <summary>
        /// 根据数据库类型获取适配器
        /// </summary>
        /// <param name="dbContext"></param>
        /// <returns></returns>
        public static DBAdapterBase GetDBAdapterBase(DbContextInner dbContext)
        {
            DBAdapterBase db = null;
            var a = DBAdapterBaseCache.TryGetValue(dbContext.DBHelper.CurrentDBType, out db);
            if (a)
            {
                return db;
            }
            db = DBConfigRegister.GetDBAdapterBase(dbContext);
            DBAdapterBaseCache.Add(dbContext.DBHelper.CurrentDBType, db);
            return db;
        }
        public abstract DBType DBType { get; }
        #region 创建结构
        /// <summary>
        ///获取列类型和默认值
        /// </summary>
        /// <param name="info"></param>
        /// <returns></returns>
        public abstract string GetColumnType(Attribute.FieldInnerAttribute info, out string defaultValue);
        /// <summary>
        /// 获取字段类型转换
        /// </summary>
        /// <returns></returns>
        public abstract Dictionary<Type, string> FieldMaping();
        static Dictionary<DBType, Dictionary<Type, string>> _FieldMaping = new Dictionary<DBType, Dictionary<Type, string>>();
        internal Dictionary<Type, string> GetFieldMaping()
        {
            if (!_FieldMaping.ContainsKey(dbType))
            {
                _FieldMaping.Add(dbType, FieldMaping());
            }
            return _FieldMaping[dbType];
        }
        /// <summary>
        /// 获取字段数据库类型
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public string GetDBColumnType(Type type)
        {
            var dic = GetFieldMaping();
            if (!type.FullName.StartsWith("System."))
            {
                //继承的枚举
                type = type.BaseType;
            }
            if (Nullable.GetUnderlyingType(type) != null)
            {
                //Nullable<T> 可空属性
                type = type.GenericTypeArguments[0];
            }
            if (type.IsEnum)
            {
                type = typeof(Enum);
            }
            if (!dic.ContainsKey(type))
            {
                throw new Exception(string.Format("找不到对应的字段类型映射 {0} 在 {1}", type, this));
            }
            return dic[type];
        }
        public abstract string GetCreateIndexScript(string owner, TableInnerAttribute table, bool unique, string indexName, params string[] columns);
        public abstract Dictionary<string, long> GetFieldLength(DbContextInner dbContext, string tableName);
        public virtual string[] StringTruncateCheckWords { get; } = new string[] { "将截断字符串", "cannot be converted to type", "string will be truncated" };
        public Exception CheckFieldLength(DbContextInner dbContext, Type type, Exception ero, System.Collections.IEnumerable datas)
        {
            #region 字段长度检查
            var table = TypeCache.GetTable(type);
            var fieldInfo = "";
            var find = false;
            foreach (var word in StringTruncateCheckWords)
            {
                if (ero.Message.Contains(word))
                {
                    find = true;
                    break;
                }
            }
            if (find)
            {
                var tableName = TypeCache.GetTableName(table.TableName, dbContext);
                var fieldLengths = GetFieldLength(dbContext, tableName);
                if (fieldLengths.Count > 0)
                {
                    var pros = table.Fields.Where(b => b.PropertyType == typeof(string));
                    var findDic = new Dictionary<string, string>();
                    foreach (var item in datas)
                    {
                        foreach (var p in pros)
                        {
                            var a = fieldLengths.TryGetValue(p.MapingName, out var length);
                            if (a)
                            {
                                var value = p.GetValue(item) + "";
                                if (value.Length > length && !findDic.ContainsKey(p.MapingName))
                                {
                                    findDic.Add(p.MapingName, $"长度大于{length} 值:[{value}]");
                                }
                            }
                        }
                    }
                    foreach (var kv in findDic)
                    {
                        fieldInfo += $"字段:[{kv.Key}] {kv.Value}";
                    }
                }
            }
            var errorMsg = $"插入错误,在表{table.TableName} {ero.Message} {fieldInfo}";
            return new Exception(errorMsg);
            #endregion
        }

        /// <summary>
        /// 增加列
        /// </summary>
        /// <param name="field"></param>
        /// <returns></returns>
        public abstract string GetCreateColumnScript(DbContextInner dbContext, Attribute.FieldInnerAttribute field);


        /// <summary>
        /// 创建存储过程
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="script"></param>
        /// <returns></returns>
        public abstract string GetCreateSpScript(string spName, string script);

        public virtual string GetDropColumnScript(string tableName, string field)
        {
            return $"alter table {KeyWordFormat(tableName)} drop column {field}";
        }
        public virtual string GetUpdateColumnScript(string tableName, Attribute.FieldInnerAttribute field)
        {
            var columnType = GetDBColumnType(field.PropertyType);
            if (field.Length > 0)
            {
                columnType = string.Format(columnType, field.Length);
            }
            return $"alter table {KeyWordFormat(tableName)} alter column {field.MapingName} {columnType}";
        }
        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="fields"></param>
        /// <param name="tableName"></param>
        public abstract void CreateTable(DbContextInner dbContext, List<Attribute.FieldInnerAttribute> fields, string tableName);
        #endregion

        #region SQL查询
        ConcurrentDictionary<Type, DataColumnCollection> cachedColumns = new ConcurrentDictionary<Type, DataColumnCollection>();
        protected DataTable GetBatchInsertTable(DbContextInner dbContext, IList details, bool keepIdentity)
        {
            var tableName = "";
            if (details.Count == 0)
                return null;
            var type = details[0].GetType();
            var table = TypeCache.GetTable(type);
            tableName = KeyWordFormat(TypeCache.GetTableName(table.TableName, dbContext));
            var tableName2 = tableName;
            var helper = dbContext.DBHelper;
            var a = cachedColumns.TryGetValue(type, out var dbColumns);
            if (!a)
            {
                //数据库结构可能和model不一致，查一次
                var sql = Core.StringBuilderCache.GetSimpleString(sb =>
                {
                    GetSelectFull(sb, "*", b =>
                    {
                        b.Append(" from " + tableName2 + " where 1=0");
                    }, "", 1);
                });
                var dt = helper.ExecDataTable(sql);
                dbColumns = dt.Columns;
                cachedColumns.TryAdd(type, dbColumns);
            }
            var tempTable = new DataTable() { TableName = tableName };
            foreach (DataColumn c in dbColumns)
            {
                tempTable.Columns.Add(c.ColumnName, c.DataType);
            }
            var typeArry = table.Fields;
            var columns = tempTable.Columns;
            foreach (var item in details)
            {
                var dr = tempTable.NewRow();
                foreach (FieldInnerAttribute info in typeArry)
                {
                    string name = info.MapingName;
                    object value = info.GetValue(item);
                    if (!keepIdentity)
                    {
                        if (info.IsPrimaryKey)
                            continue;
                    }
                    if (info.ValueNeedConvert)
                    {
                        value = SettingConfig.ValueObjSerializer(value);
                    }
                    var value2 = ObjectConvert.CheckNullValue(value, info.PropertyType);
                    var c = columns[name];
                    if (c.DataType != info.PropertyType)
                    {
                        value2 = ObjectConvert.ConvertObject(c.DataType, value2);
                        if (value2 is null)
                        {
                            value2 = DBNull.Value;
                        }
                    }
                    dr[name] = value2;
                }
                tempTable.Rows.Add(dr);
            }
            return tempTable;
        }
        /// <summary>
        /// 批量插入方法
        /// </summary>
        /// <param name="details"></param>
        /// <param name="keepIdentity">否保持自增主键</param>
        public virtual void BatchInsert(DbContextInner dbContext, IList details, bool keepIdentity = false)
        {
            throw new NotImplementedException();
            //if (details.Count == 0)
            //    return;
            //var helper = dbContext.DBHelper;
            //var tempTable = GetBatchInsertTable(dbContext, details, keepIdentity, out var tableName);
            //helper.InsertFromDataTable(tempTable, tableName, keepIdentity);
        }
        public virtual async Task BatchInsertAsync(DbContextInner dbContext, IList details, bool keepIdentity = false)
        {
            //if (details.Count == 0)
            //    return;
            //var helper = dbContext.DBHelper;
            //var tempTable = GetBatchInsertTable(dbContext, details, keepIdentity, out var tableName);
            //await helper.InsertFromDataTableAsync(tempTable, tableName, keepIdentity);
            BatchInsert(dbContext, details, keepIdentity);
            await Task.FromResult(1);
        }
        protected string GetBatchInsertSql(DbContextInner dbContext, System.Collections.IList details, bool keepIdentity = false)
        {
            var obj = details[0];
            Type type = obj.GetType();
            var helper = dbContext.DBHelper;
            helper.ClearParams();
            //INSERT INTO table_name (column1, column2) VALUES (?, ?), (?, ?), ...
            var table = TypeCache.GetTable(type);
            var fields = table.Fields.Where(b => !b.IsPrimaryKey);
            if (keepIdentity)
            {
                fields = table.Fields.AsEnumerable();
            }
            var sb = new StringBuilder();
            var sql = GetInsertSql(dbContext, table, obj);
            sb.Append(sql);
            var isFirst = true;
            foreach (var obj2 in details)
            {
                if(isFirst)
                {
                    isFirst = false;
                    continue;
                }
      
                var parameNames = new List<string>();
                foreach (var f in fields)
                {
                    if (f.IsPrimaryKey && !keepIdentity)
                    {
                        continue;
                    }
                    dbContext.parIndex += 1;
                    var value = valueFormat(f, obj2);
                    var par = $"_{f.MapingName}{dbContext.parIndex}";
                    var parameName = GetParamName(par, "");
                    parameNames.Add(parameName);
                    helper.AddParam(par, value);
                }
                sb.Append($",({string.Join(",", parameNames)})");
            }
            return sb.ToString();
        }
        public virtual void InsertOrUpdate(DbContextInner dbContext, IList items, InsertOrUpdateOption option)
        {
            throw new NotImplementedException();
        }
        public abstract string DateTimeFormat(string field, string format);

        /// <summary>
        /// 查询表所有字段名
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public abstract string GetTableFields(string tableName);
        /// <summary>
        /// 获取UPDATE语法
        /// </summary>
        /// <param name="table"></param>
        /// <param name="setString"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public virtual string GetUpdateSql(string table, string setString, string where)
        {
            string sql = string.Format("update {0} set {1} {2}", KeyWordFormat(table), setString, where);
            return sql;
        }
        /// <summary>
        /// 获取删除语法
        /// </summary>
        /// <param name="table"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public virtual string GetDeleteSql(string table, string where)
        {
            string sql = string.Format("delete from {0}{1}", KeyWordFormat(table), where);
            return sql;
        }

        /// <summary>
        /// 插入对象
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public abstract object InsertObject<T>(DbContextInner dbContext, T obj);

        public virtual Task<object> InsertObjectAsync<T>(DbContextInner dbContext, T obj)
        {
            throw new NotImplementedException("未实现InsertObjectAsync");
        }

        static System.Collections.Concurrent.ConcurrentDictionary<string, string> insertSqlCache = new System.Collections.Concurrent.ConcurrentDictionary<string, string>();

        protected object valueFormat(FieldInnerAttribute info, object obj)
        {
            object value = info.GetValue(obj);
            value = ObjectConvert.CheckNullValue(value, info.PropertyType);
            if (info.ValueNeedConvert)
            {
                value = SettingConfig.ValueObjSerializer(value);
            }
            return value;
        }
        protected string GetInsertSql(DbContextInner dbContext, Attribute.TableInnerAttribute table, object obj, bool fillParame = true)
        {
            Type type = obj.GetType();

            var helper = dbContext.DBHelper;
            var tableName = table.TableName;
            tableName = TypeCache.GetTableName(tableName, dbContext);
            //var key = string.Format("{0}_{1}", tableName, fillParame);
            var key = $"{dbContext.DBHelper.DatabaseName}_{tableName}_{fillParame}";
            //var primaryKey = table.PrimaryKey;
            var typeArry = table.Fields;
            //var reflect = ReflectionHelper.GetInfo<T>();
            string sql;
            var cached = insertSqlCache.TryGetValue(key, out sql);
            if (!cached)
            {
                sql = string.Format("insert into {0}(", KeyWordFormat(tableName));
            }
            string sql1 = "";
            string sql2 = "";
            foreach (var info in typeArry)
            {
                string name = info.MapingName;
                var keepIdentity = info.KeepIdentity;
                if(info.IsPrimaryKey)
                {
                    var isInt = info.PropertyType == typeof(int) || info.PropertyType == typeof(long);
                    if (isInt)
                    {
                        var keyValue = info.GetValue(obj);
                        if (Convert.ToInt64(keyValue) > 0)//为数值类型，有值，保留主键
                        {
                            keepIdentity = true;
                        }
                    }
                    if (keepIdentity == false)
                    {
                        continue;
                    }
                }
                //if (info.IsPrimaryKey && !info.KeepIdentity && DBType != DBType.ORACLE)
                //{
                //    continue;
                //}
                var value = valueFormat(info, obj);
                if (!cached)
                {
                    sql1 += string.Format("{0},", FieldNameFormat(info));
                    if (fillParame)
                    {
                        var par = GetParamName(name, "");
                        sql2 += string.Format("{0},", par);//@{0}
                    }
                }
                if (fillParame)
                {
                    helper.AddParam(name, value);
                }
            }
            if (!cached)
            {
                sql1 = sql1.Substring(0, sql1.Length - 1) + ") values";
                sql += sql1;
                if (fillParame)
                {
                    sql2 = sql2.Substring(0, sql2.Length - 1);
                    sql += "( " + sql2 + ")";
                }
                //sql = SqlFormat(sql);
                insertSqlCache.TryAdd(key, sql);
            }
            return sql;
        }
        /// <summary>
        /// 获取查询前几条
        /// </summary>
        /// <param name="fields"></param>
        /// <param name="query"></param>
        /// <param name="top"></param>
        /// <returns></returns>
        public abstract void GetSelectFull(StringBuilder sb, string fields, Action<StringBuilder> query, string sort, int top);

        /// <summary>
        /// 获取with nolock语法
        /// </summary>
        /// <returns></returns>
        public abstract string GetWithNolockFormat(bool v);
        /// <summary>
        /// 获取where条件
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public virtual string GetWhere(string where)
        {
            return where.Length == 0 ? "" : " where " + where;
        }
        #endregion

        #region  系统查询
        /// <summary>
        /// 获取所有存储过程
        /// </summary>
        /// <returns></returns>
        public abstract string GetAllSPSql(string db);
        /// <summary>
        /// 获取所有表,查询需要转为小写
        /// </summary>
        /// <returns></returns>
        public abstract string GetAllTablesSql(string db);
        #endregion

        #region 模版
        /// <summary>
        /// 存储过程参数格式化
        /// </summary>
        /// <param name="name"></param>
        /// <param name="type"></param>
        /// <param name="output"></param>
        /// <returns></returns>
        public abstract string SpParameFormat(string name, string type, bool output);
        /// <summary>
        /// 关键字格式化,如SQL为 [field]
        /// </summary>
        public virtual string KeyWordFormat(string value)
        {
            return value;
        }
        public string FieldNameFormat(Attribute.FieldInnerAttribute field)
        {
            if (string.IsNullOrEmpty(field.MapingNameFormat))
            {
                field.MapingNameFormat = KeyWordFormat(field.MapingName);
            }
            return field.MapingNameFormat;
        }
        /// <summary>
        /// GROUP分页模版
        /// </summary>
        public abstract string TemplateGroupPage { get; }
        /// <summary>
        /// 查询分页模版
        /// </summary>
        public abstract string TemplatePage { get; }
        /// <summary>
        /// 存储过程模版
        /// </summary>
        public abstract string TemplateSp { get; }
        /// <summary>
        /// 语句自定义格式化处理
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public virtual string SqlFormat(string sql)
        {
            return sql;
        }
        int parIndex = 1;
        /// <summary>
        /// 提取SQL参数
        /// </summary>
        /// <param name="db"></param>
        /// <param name="sql"></param>
        /// <param name="manual"></param>
        /// <returns></returns>
        public virtual string ReplaceParameter(DBHelper db, out Dictionary<string, string> parame, string sql, bool manual = false)
        {
            parame = new Dictionary<string, string>();
            if (!SettingConfig.ReplaceSqlParameter && !manual)
            {
                return sql;
            }
            //return sql;
            var re = @"((\s|,)*)(\w+)\s*(>|<|=|!=|>=|<=)\s*('(.*?)'|([1-9]\d*.\d*|0.\d*[1-9]\d*))(\s|,|\))";
            sql = sql + " ";
            if (!Regex.IsMatch(sql, re, RegexOptions.IgnoreCase))
            {
                return sql;
            }
            Regex r = new Regex(re, RegexOptions.IgnoreCase);
            //List<string> pars = new List<string>();
            //int index = 1;
            for (var m = r.Match(sql); m.Success; m = m.NextMatch())
            {
                var name = m.Groups[3];
                var op = m.Groups[4];
                var value1 = m.Groups[6];
                var value2 = m.Groups[7];
                var value = string.IsNullOrEmpty(value2.Value) ? value1 : value2;
                var p = m.Groups[1];
                var p2 = m.Groups[8];
                var pName = GetParamName("_p", parIndex);
                if (db != null)
                {
                    db.AddParam(pName, value.ToString());
                }
                else
                {
                    parame.Add(pName, value.ToString());
                }
                sql = sql.Replace(m.ToString(), string.Format("{0}{1}{4}{2}{3} ", p, name, pName, p2, op));
                parIndex += 1;
            }
            return sql;
        }
        #endregion

        #region 函数语法
        public virtual string SubstringFormat(string field, int index, int length)
        {
            return string.Format(" SUBSTRING({0},{1},{2})", field, index, length);
        }

        public virtual string StringLikeFormat(string field, string parName)
        {
            return string.Format("{0} LIKE {1}", field, parName);
        }

        public virtual string StringNotLikeFormat(string field, string parName)
        {
            return string.Format("{0} NOT LIKE {1}", field, parName);
        }

        public virtual string StringContainsFormat(string field, string parName)
        {
            return string.Format("CHARINDEX({1},{0})>0", field, parName);
        }
        public virtual string StringNotContainsFormat(string field, string parName)
        {
            return string.Format("CHARINDEX({1},{0})<=0", field, parName);
        }

        public virtual string BetweenFormat(string field, string parName, string parName2)
        {
            return string.Format("{0} between {1} and {2}", field, parName, parName2);
        }
        public virtual string NotBetweenFormat(string field, string parName, string parName2)
        {
            return string.Format("{0} not between {1} and {2}", field, parName, parName2);
        }
        public virtual string DateDiffFormat(string field, string format, string parName)
        {
            return string.Format("DateDiff({0},{1},{2})", format, field, parName);
        }

        public virtual string InFormat(string field, string parName)
        {
            return string.Format("{0} IN ({1})", field, parName);
        }
        public virtual string NotInFormat(string field, string parName)
        {
            return string.Format("{0} NOT IN ({1})", field, parName);
        }
        public abstract string CastField(string field, Type fieldType);
        public virtual string IsNotFormat(bool isNot)
        {
            return isNot ? " is not " : " is ";
        }
        public virtual string ToUpperFormat(string field)
        {
            return string.Format("upper({0})", field);
        }
        public virtual string ToLowerFormat(string field)
        {
            return string.Format("lower({0})", field);
        }
        public virtual string IsNull(string field, object value)
        {
            return string.Format("isnull({0},{1})", field, value);
        }
        public virtual string LengthFormat(string field)
        {
            return string.Format("len({0})", field);
        }
        public virtual string Trim(string field)
        {
            return string.Format("ltrim(rtrim({0})) ", field);
        }
        public virtual string TrimStart(string field)
        {
            return string.Format("ltrim({0}) ", field);
        }
        public virtual string TrimEnd(string field)
        {
            return string.Format("rtrim({0}) ", field);
        }
        public virtual string Replace(string field, string find, string rep)
        {
            return string.Format("replace({0},{1},{2}) ", field, find, rep);
        }
        public virtual string Distinct(string field)
        {
            return string.Format("Distinct({0}) ", field);
        }
        public virtual string DistinctCount(string field)
        {
            return string.Format("count(Distinct({0})) ", field);
        }
        #endregion

        /// <summary>
        /// 分页SQL 默认为MSSQL
        /// </summary>
        /// <param name="db"></param>
        /// <param name="fields"></param>
        /// <param name="rowOver"></param>
        /// <param name="condition"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <param name="sort"></param>
        /// <returns></returns>
        public abstract string PageSqlFormat(DBHelper db, string fields, string rowOver, string condition, int start, int end, string sort);
        /// <summary>
        /// 获取关联更新语名
        /// </summary>
        /// <param name="t1"></param>
        /// <param name="t2"></param>
        /// <param name="condition"></param>
        /// <param name="setValue"></param>
        /// <returns></returns>
        public abstract string GetRelationUpdateSql(string t1, string t2, string condition, string setValue, LambdaQuery.LambdaQueryBase query);
        /// <summary>
        /// 获取关联删除语句
        /// </summary>
        /// <param name="t1"></param>
        /// <param name="t2"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public abstract string GetRelationDeleteSql(string t1, string t2, string condition, LambdaQuery.LambdaQueryBase query);
        public abstract string GetFieldConcat(string field, object value, Type type);
        /// <summary>
        /// 参数名
        /// </summary>
        public abstract string GetParamName(string name, object index);

        public abstract string GetSplitFirst(string field, string parName);

        public virtual void UpdateTableComment(DBHelper db, string table, string comment)
        {
            var script = $"execute sp_addextendedproperty  'MS_Description','{comment}','user','dbo','table','ProductData','column','InterFaceUser';";
        }
        public virtual void UpdateFieldComment(DBHelper db, string table, FieldInnerAttribute field, string comment)
        {
            var script = $"execute sp_addextendedproperty  'MS_Description','{comment}','user','dbo','table','{table}','column','{field}';";
        }
    }
}
